#include "sqlite.h"

Sqlite::Sqlite()
{
    db = QSqlDatabase::addDatabase("QSQLITE","sqlite2");
    db.setDatabaseName("info1.db");
}

//建立数据库连接
bool Sqlite::createConnection()
{
    if(!db.open())
    {
        qDebug() << "无法建立数据库连接";
        return false;
    }
    qDebug()<<"数据库连接成功";
    return true;
}

//创建数据库表
bool Sqlite::createTable()
{
    QSqlQuery query(db);
    QString create_table = "create table goods(id int primary key,name varchar,number int)";
    query.prepare(create_table);
    query.exec();
    return true;
}

//插入一条记录
bool Sqlite::insertData(int id , QString name ,int number)
{
    QSqlQuery query(db);
    QString insertSql = "insert into goods values(?,?,?)";
    query.prepare(insertSql);
    query.addBindValue(id);
    query.addBindValue(name);
    query.addBindValue(number);
    if(!query.exec())
    {
        qDebug()<<"插入失败";
        return false;
    }
    else
    {
        qDebug()<<"插入成功";
        return true;
    }
}

//删除一条记录
bool Sqlite::deleteData(int id)
{
    int ret = queryData(id);
    if(0 == ret)
    {
        return false;
    }
    QSqlQuery query(db);
    QString deleteSql = "delete from goods where id = ?";
    query.prepare(deleteSql);
    query.addBindValue(id);

    if(!query.exec())
    {
        qDebug()<<"删除失败";
        return false;
    }
    else
    {
        qDebug()<<"删除成功";
        return true;
    }
}

//清空表
bool Sqlite::deleteAll()
{
    QSqlQuery query(db);
    QString deleteSql = "delete from goods ";
    query.prepare(deleteSql);
    if(!query.exec())
    {
        qDebug()<<"删除所有信息失败";
        return false;
    }
    else
    {
        qDebug()<<"删除所有信息成功";
        return true;
    }
}

//根据商品的id查询该商品的数量
int Sqlite::queryData(int id)
{
    QSqlQuery query(db);
    QString selectSql = "select * from goods where id = ?";
    query.prepare(selectSql);
    query.addBindValue(id);
    if(!query.exec())
    {
        qDebug() << "查询失败" << endl;;
        return 0;
    }
    else
    {
        query.next();
        qDebug() << "查询成功!" << endl;
        int id2 = query.value(0).toInt();
        QString name = query.value(1).toString();
        int number = query.value(2).toInt();
        qDebug()<<QString("id:%1    name:%2     number:%3").arg(id2).arg(name).arg(number);
        return number;
    }

}

//查询所有信息 存放到goods数组中
int Sqlite::queryAll(Goods goods[],int n)
{
    QSqlQuery query(db);
    QString selectSql = "select * from goods";
    query.prepare(selectSql);
    int i = 0;
    if(!query.exec())
    {
        qDebug()<<"查询失败";
        return 0;
    }
    else
    {
        while(query.next())
        {
            int id2 = query.value(0).toInt();
            QString name = query.value(1).toString();
            int number = query.value(2).toInt();
            goods[i].setId(id2);
            goods[i].setName(name);
            goods[i].setNumber(number);
            i++;
            //    qDebug()<<QString("id:%1    name:%2     number:%3").arg(id2).arg(name).arg(number);
        }
        qDebug() << "查询所有商品信息成功!" << endl;
        return i;
    }
}

//修改商品数量
bool Sqlite::updateOne(int id,int number)
{
    QSqlQuery query(db);
    QString updateSql = "update goods set number = ? where id = ?";
    query.prepare(updateSql);
    query.addBindValue(number);
    query.addBindValue(id);
    if(!query.exec())
    {
        qDebug() << "修改失败" << endl;
        return false;
    }
    else
    {
        qDebug() << "修改成功!" << endl;
        return true;
    }
}

//删除表操作
bool Sqlite::droptable()
{
    QSqlQuery query(db);
    QString drop = "drop table goods";
    query.prepare(drop);
    if(!query.exec())
    {
        qDebug()<<"删除商品表失败!";
        return false;
    }
    else
    {
        qDebug()<<"删除商品表成功!";
        return true;
    }
}

//关闭数据库
void Sqlite::closesql()
{
    db.close();
}
